const {
    sqlExec,
    sqlExecInTransaction,
    startTransaction,
    commitTransaction,
    rollbackTransaction
} = require('../db/mysql57');

const minio = require('../db/minio');

const circleDao = {
    async getCircle(uid, lastId) {
        let sql = `select
                c.id as id,
                c.uid as uid,
                c.title as title,
                DATE_FORMAT(c.time, '%Y-%m-%d %H:%i:%s') as time,
                c.content as content,
                c.photoId as photoId,
                (select count(*) from CircleLike as cl1 where cl1.uid = ? and cl1.circleId = c.id) as isLike,
                (select count(*) from CircleLike as cl2 where cl2.circleId = c.id) as likeCount,
                (select count(*) from CircleComment as cc where cc.circleId = c.id) as commentCount
            from Circle as c
            order by c.time desc
            LIMIT 3`;
        let data = [uid];
        if (lastId) {
            sql = `select
                c.id as id,
                c.uid as uid,
                c.title as title,
                DATE_FORMAT(c.time, '%Y-%m-%d %H:%i:%s') as time,
                c.content as content,
                c.photoId as photoId,
                (select count(*) from CircleLike as cl1 where cl1.uid = ? and cl1.circleId = c.id) as isLike,
                (select count(*) from CircleLike as cl2 where cl2.circleId = c.id) as likeCount,
                (select count(*) from CircleComment as cc where cc.circleId = c.id) as commentCount
            from Circle as c
            where c.time <= (
                select c1.time from Circle as c1 where c1.id = ? LIMIT 1
            ) and c.id != ?
            order by c.time desc
            LIMIT 3`;
            data = [ uid, lastId, lastId ];
        }
        return sqlExec(sql, data);
    },
    // 获取圈子详情
    async getCircleInfo(uid, circleId) {
        let sql = `select
                c.id as id,
                c.uid as uid,
                c.title as title,
                DATE_FORMAT(c.time, '%Y-%m-%d %H:%i:%s') as time,
                c.content as content,
                c.photoId as photoId,
                (select count(*) from CircleLike as cl1 where cl1.uid = ? and cl1.circleId = c.id) as isLike,
                (select count(*) from CircleLike as cl2 where cl2.circleId = c.id) as likeCount,
                (select count(*) from CircleComment as cc where cc.circleId = c.id) as commentCount
            from Circle as c
            where c.id = ?
            LIMIT 1`;
        let data = [uid, circleId];
        return sqlExec(sql, data);
    },
    // 评论圈子
    async comment(uid, circleId, content) {
        let sql = `insert into CircleComment(
                uid, circleId, content
            ) VALUES (?, ?, ?)`;
        let data = [uid, circleId, content];
        return sqlExec(sql, data);
    },
    /*
     * 获取所有评论信息
     */
    async getComment(circleId) {
        let sql = `select
                cc.id as id,
                u.username as name,
                cc.content as comment,
                DATE_FORMAT(cc.time, '%Y-%m-%d %H:%i:%s') as time
            from CircleComment as cc
            left join User as u on u.id = cc.uid
            where cc.circleId = ?
            order by cc.time desc`;
        let data = [circleId];
        return sqlExec(sql, data);
    },
    /*
     * 点赞圈子
     */
    async likeCircle(uid, circleId) {
        let sql = `insert into CircleLike(
                uid, circleId
            ) (select ?, ? where (
                select count(*) from CircleLike where uid = ? and circleId = ?
            ) = 0)`;
        let data = [uid, circleId, uid, circleId];
        return sqlExec(sql, data);
    },
    /*
     * 取消点赞圈子
     */
    async nolikeCircle(uid, circleId) {
        let sql = `delete from CircleLike where uid = ? and circleId = ?`;
        let data = [uid, circleId];
        return sqlExec(sql, data);
    },
    async getAllCircle(uid, page) {
        let sql = `SELECT
                c.id as id,
                c.uid as uid,
                u.username as username,
                c.title as title,
                DATE_FORMAT(c.time, '%Y-%m-%d %H:%i:%s') as time
            FROM Circle as c
            left join User as u on u.id = c.uid
            order by c.time desc
            LIMIT ?, 50`;
        let data = [ (page-1) * 50 ];
        return sqlExec(sql, data);
    },

    // 发布圈子
    async addCircle(uid, circleTitle, circleContent, selectedImage) {
        let connection;
        // 开启事务
        try {
            connection = await startTransaction();
        } catch (err) {
            throw err;
        }
        // 执行sql
        let result;
        try {
            // 创建图片记录
            let sql1 = `insert into PublicImg(
                    browser, uri, status
                ) VALUES (?, ?, ?)`;
            let data1 = [ 'public', '', '待上传' ];
            result = await sqlExecInTransaction(connection, sql1, data1);
            let insertId = result.insertId;
            // 上传图片
            // 提取 base64 数据部分
            let base64Data = selectedImage.split(',')[1];
            // 将 base64 数据转换为 Buffer
            let buffer = Buffer.from(base64Data, 'base64');
            // 定义 MinIO 的 bucket 名称和对象名称
            let bucketName = 'public';                   // 替换为你的 bucket 名称
            let objectName = `public-${insertId}.png`;   // 替换为你希望在 MinIO 中存储的对象名称
            // 上传 Buffer 到 MinIO
            await minio.minio.putObject(bucketName, objectName, buffer, buffer.length, async function (err, etag) {
                if (err) {
                    await rollbackTransaction(connection);
                    isRollback = true;
                    throw err;
                }
            });
            // 补充图片记录
            let sql2 = `update PublicImg set
                    uri = ?,
                    status = '已完成'
                where id = ?`;
            let data2 = [ objectName, insertId ];
            await sqlExecInTransaction(connection, sql2, data2);
            // 创建圈子记录
            let sql3 = `insert into Circle(
                    uid, title, content, photoId
                ) VALUES (?, ?, ?, ?)`;
            let data3 = [ uid, circleTitle, circleContent, insertId ];
            result = await sqlExecInTransaction(connection, sql3, data3);
            commitTransaction(connection);
            return result;
        } catch (err) {
            rollbackTransaction(connection);
            throw err;
        }
    },
    async deleteCircle(uid, circleId) {
        let sql = `delete from Circle where uid = ? and id = ?`;
        let data = [ uid, circleId ];
        return sqlExec(sql, data);
    },

    async adminDeleteCircle(circleId) {
        let sql = `delete from Circle where id = ?`;
        let data = [ circleId ];
        return sqlExec(sql, data);
    }
};

module.exports = circleDao;